package kines.ct;

import kines.SerializableDB;
import javax.servlet.http.HttpSession;
import java.util.Calendar;
import java.util.ArrayList;
import java.sql.*;
import java.util.Arrays;

/**
 * <p>Title: CT</p>
 * <p>Description: Intranet Consorcio Terrenos</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: Consorcio Terrenos</p>
 * @author Jose Delgado
 * @version 1.0
 */

public class Quotation implements SerializableDB {
  /**
   * Spanish Month's Names
   */
  private String monthNames[] = {"ENERO","FEBRERO","MARZO","ABRIL","MAYO","JUNIO","JULIO","AGOSTO","SEPTIEMBRE","OCTUBRE","NOVIEMBRE","DICIEMBRE"};
  /**
   * Identifier in DB
   */
  private int id;
  /**
   * Development Code assigned to this prospect
   */
  private String developmentCode = null;
  /**
   * Sets Development Code of prospect
   * @param developmentCode
   */
  public void setDevelopmentCode( String developmentCode ){
    this.developmentCode = developmentCode;
  }
  /**
   * Gets Development Code of prospect
   * @return Development Code of prospect
   */
  public String getDevelopmentCode( ){
    return developmentCode;
  }
  /**
   * Quotation over this terrain
   */
  private String terrainCode = null;
  /**
   * Quotation for this prospect
   */
  private int prospectId = 0;
  /**
   * Quotation Date
   */
  private Calendar dateQuotation = Calendar.getInstance();
  /**
   * Sets identifier
   * @param Quotation Id
   */
  protected void setId( int id ){
    this.id = id;
  }
  /**
   * Gets identifier attribute
   * @return id
   */
  public int getId( ){
    return id;
  }
  /**
   * Sets Terrain Code
   * @param Terrain Code
   * @throws java.lang.Exception
   */
  public void setTerrain( String terrainCode ){
    this.terrainCode = terrainCode;
  }
  /**
   * Gets Terrain of this quotation
   * @return Terrain Object
   * @throws java.lang.Exception
   */
  public Terrain getTerrain( )throws Exception{
    return Terrain.getData( terrainCode );
  }
  /**
   * Sets Prospect Id
   * @param Prospect Id
   */
  public void setProspectId( int prospectId ){
    this.prospectId = prospectId;
  }
  /**
   * Gets Prospect Id
   * @return Prospect Object
   */
  public Prospect getProspect( )throws Exception{
    return Prospect.getData( prospectId );
  }
  /**
   * Sets Quotation Date
   * @param Quotation Date
   */
  protected void setQuotationDate( int year, int month, int day ){
    dateQuotation.set( year, month, day );
  }
  /**
   * Gets Quotation Date
   * @param Quotation Date
   */
  public Calendar getQuotationDate( ){
    return dateQuotation;
  }
  /**
   * Gets Quotation Date in Str format
   * @return Quotation Date in Str format
   */
  public String getQuotationDateStr( ){
    return dateQuotation.get(Calendar.DATE)+"/"+monthNames[dateQuotation.get( Calendar.MONTH)]+"/"+dateQuotation.get(Calendar.YEAR);
  }
  /**
   * Gets All Quotations of this Salesperson
   * @param salespersonCode Salesperson Code
   * @return
   * @throws java.lang.Exception
   */
  public static Object[] getData( String salespersonCode )throws Exception{
    ArrayList quotations = new ArrayList( );
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_quotation = ConnRs.createStatement();
    String str_quotation = "set language english  select * from c_view_cotizaciones where cvevendedor = '"+salespersonCode+"'";
    ResultSet rs_quotation = stmt_quotation.executeQuery(str_quotation);
    while( rs_quotation.next() ){
      quotations.add( Quotation.getQuotation( rs_quotation ) );
    }
    ConnRs.close();
    return quotations.toArray();
  }
  /**
   * Gets Quotations list
   * @param where clause
   * @param order clause
   * @return array of Quotations
   * @throws java.lang.Exception
   */
  public static Object[] getData(  String salespersonCode, String where , String order )throws Exception{
    ArrayList quotations = new ArrayList( );
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_quotation = ConnRs.createStatement();
    String str_quotation = "select * from c_view_cotizaciones where cvevendedor = '"+salespersonCode+"' ";
    str_quotation += (where !=null && !where.equals("")?" and "+where:"");
    str_quotation += (order !=null && !order.equals("")?" order by "+order:"");
    ResultSet rs_quotation = stmt_quotation.executeQuery(str_quotation);
    while( rs_quotation.next() ){
      quotations.add( Quotation.getQuotation( rs_quotation ) );
    }
    ConnRs.close();
    return quotations.toArray();
  }
  /**
   * Gets Quotation
   * @param quotationId Quotation Identifier
   * @return Object Quotation
   * @throws java.lang.Exception if it's Happend some exception in the query
   */
  public static Quotation getData( int quotationId )throws Exception{
    Quotation qRet = null;
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_quotation = ConnRs.createStatement();
    String str_quotation = "select * from c_view_cotizaciones where id_cotizacion = "+quotationId;
    ResultSet rs_quotation = stmt_quotation.executeQuery(str_quotation);
    if( rs_quotation.next() ){
      qRet = Quotation.getQuotation( rs_quotation );
    }
    ConnRs.close();
    return qRet;
  }
  /**
   * Fills a Quotation from a ResultSet
   * @param rs_quotation ResultSet where Quotation is
   * @return Quotation
   * @throws java.lang.Exception
   */
  private static Quotation getQuotation( ResultSet rs_quotation )throws Exception{
    Quotation q = null;
    if( rs_quotation != null ){
      q = new Quotation( );
      q.setId( rs_quotation.getInt("id_cotizacion") );
      q.setProspectId( rs_quotation.getInt("id_prospecto") );
      q.setTerrain( rs_quotation.getString("CveLote") );
      q.setDevelopmentCode( rs_quotation.getString("CveDesarrollo") );
      Date dt1 = rs_quotation.getDate("fecha_cotizacion");
      Calendar c = Calendar.getInstance();
      c.setTime( (java.util.Date)dt1 );
      q.setQuotationDate( c.get(Calendar.YEAR), c.get(Calendar.MONTH),c.get(Calendar.DATE) );
    }
    return q;
  }
  /**
   * Gets All Plans Sales Plan
   * @return Array of plans
   */
  public Object[] getQuotationSalesPlanAll( )throws Exception{
    return QuotationSalesPlan.getPlansAll( id );
  }
  /**
   * Gets Sales Plan of this Quotation
   * @return Array of plans
   */
  public Object[] getQuotationSalesPlan( )throws Exception{
    return QuotationSalesPlan.getPlans( id );
  }
  /**
   * Save quotations
   * @param session
   * @throws java.lang.Exception
   */
  public void saveData(HttpSession session) throws Exception {
    if( prospectId != -1 && terrainCode != null ){
      Driver DriverRs = (Driver)Class.forName(driver).newInstance();
      Connection ConnRs = DriverManager.getConnection(strconn, username,
          password);
      Statement stmt_quotation = ConnRs.createStatement();
      String str_quotation = "INSERT INTO c_cotizacion (CveLote, id_prospecto, CveDesarrollo, fecha_cotizacion) "+
          "VALUES('"+terrainCode+"', "+prospectId+", '"+developmentCode+"', now())";
      //System.out.println("SQL->" + str_quotation);
      stmt_quotation.executeUpdate( str_quotation );
      stmt_quotation.close();
      Statement stmt_get_id_quotation = ConnRs.createStatement();
      String str_get_id_quotation = "select case when max(id_cotizacion) is null then 0 else max(id_cotizacion) end as id from c_cotizacion";
      ResultSet rs_get_id_quotation = stmt_get_id_quotation.executeQuery(str_get_id_quotation);
      if (rs_get_id_quotation.next()) {
        id = rs_get_id_quotation.getInt("id");
      }
      rs_get_id_quotation.close();
      stmt_get_id_quotation.close();
    }
  }
  /**
   * Not Implement
   * @return
   * @throws java.lang.Exception
   */
  public boolean deleteData() throws Exception {
    /**@todo Implement this kines.SerializableDB method*/
    throw new java.lang.UnsupportedOperationException("Method deleteData() not yet implemented.");
  }
  /**
   * Check if sales plan is in quotation
   * @param salesPlan to check
   * @return true if sales plan is in quotation, false otherwise
   * @throws java.lang.Exception
   */
  public boolean isInQuotation( SalesPlan salesPlan )throws Exception{
    boolean retVal = false;
    Object [] quotationSalesPlan = getQuotationSalesPlan( );
    for( int i = 0; i < quotationSalesPlan.length; i++ ){
      QuotationSalesPlan quotationSalePlan = (QuotationSalesPlan)quotationSalesPlan[i];
      if( salesPlan.getNumber() == quotationSalePlan.getSalesPlan().getNumber() ){
        retVal = true;
        break;
      }
    }
    return retVal;
  }
}